#!/usr/bin/env python
# -*- coding: utf-8 -*-

''' Autor: Bruno Alves '''


import sqlite3
import sys
import string
import csv

''' Class to take care of the statistics and the csv files. '''
class Statistics:
    def __init__(self):
        '''Class constructor'''

        self.dataList = []
        
        connection = sqlite3.connect("Database.db")
        connection.text_factory = str  #to avoid erros when selecting non-utf data

        cursor = connection.cursor()

        cursor.execute('''select * from Data''')

        for line in cursor.fetchall():
            self.dataList.append([line[0], line[1].replace('♦', '"'), line[2].replace('♦', '"'), line[3].replace('♦', '"'),
                             line[4].replace('♦', '"'), line[5].replace('♦', '"'), line[6], line[7], line[8], line[9],
                             line[10], line[11], line[12], line[13], line[14], line[15], line[16], line[17], line[18],
                             line[19], line[20], line[21]])

            pass


        self.course = set()
        self.education_level = set()

             
        for line in self.dataList:
            self.education_level.add(line[3]) 
            self.course.add(line[4])
            pass


        self.course = list(self.course)
        self.education_level = list(self.education_level)

        self.course.sort()
        self.education_level.sort()

        connection.close()
        pass


    def statistic1(self):
        '''Method to create the csv file of the first statistic (courses with 'computadores' or 'informática')'''

        courses_informatica = set()

        for x in range(len(self.course)):
            if ((self.course[x].lower().find('computadores') != -1) or (self.course[x].lower().find('informática') != -1)):
                courses_informatica.add(self.course[x])
                pass
            pass

        courses_informatica = list(courses_informatica)
        courses_informatica.sort()

        file1 = open('CSVStatistic1.csv', 'wb')
        spamWriter1 = csv.writer(file1, delimiter=',')

        spamWriter1.writerow(["Course", "Establishment",
                              "Organic Unit", "Year 1995-1996",
                              "Year 1996-1997", "Year 1997-1998",
                              "Year 1998-1999", "Year 1999-2000",
                              "Year 2000-2001", "Year 2001-2002",
                              "Year 2002-2003", "Year 2003-2004",
                              "Year 2004-2005", "Year 2005-2006",
                              "Year 2006-2007", "Year 2007-2008",
                              "Year 2008-2009", "Year 2009-2010",
                              "Year 2010-2011"])


        for x in range(len(courses_informatica)):
            for line in self.dataList:
                if(courses_informatica[x] == line[4]):
                    if(line[6] != '-'):
                        course9596 = '+'
                        pass
                    else:
                        course9596 = '-'
                        pass

                    if(line[7] != '-'):
                        course9697 = '+'
                        pass
                    else:
                        course9697 = '-'
                        pass

                    if(line[8] != '-'):
                        course9798 = '+'
                        pass
                    else:
                        course9798 = '-'
                        pass

                    if(line[9] != '-'):
                        course9899 = '+'
                        pass
                    else:
                        course9899 = '-'
                        pass

                    if(line[10] != '-'):
                        course9900 = '+'
                        pass
                    else:
                        course9900 = '-'
                        pass

                    if(line[11] != '-'):
                        course0001 = '+'
                        pass
                    else:
                        course0001 = '-'
                        pass

                    if(line[12] != '-'):
                        course0102 = '+'
                        pass
                    else:
                        course0102 = '-'
                        pass

                    if(line[13] != '-'):
                        course0203 = '+'
                        pass
                    else:
                        course0203 = '-'
                        pass


                    if(line[14] != '-'):
                        course0304 = '+'
                        pass
                    else:
                        course0304 = '-'
                        pass

                    if(line[15] != '-'):
                        course0405 = '+'
                        pass
                    else:
                        course0405 = '-'
                        pass

                    if(line[16] != '-'):
                        course0506 = '+'
                        pass
                    else:
                        course0506 = '-'
                        pass

                    if(line[17] != '-'):
                        course0607 = '+'
                        pass
                    else:
                        course0607 = '-'
                        pass

                    if(line[18] != '-'):
                        course0708 = '+'
                        pass
                    else:
                        course0708 = '-'
                        pass

                    if(line[19] != '-'):
                        course0809 = '+'
                        pass
                    else:
                        course0809 = '-'
                        pass

                    if(line[20] != '-'):
                        course0910 = '+'
                        pass
                    else:
                        course0910 = '-'
                        pass

                    if(line[21] != '-'):
                        course1011 = '+'
                        pass
                    else:
                        course1011 = '-'
                        pass

                    spamWriter1.writerow([courses_informatica[x], line[1], line[5], course9596, course9697, course9798, course9899, course9900,
                                  course0001, course0102, course0203, course0304, course0405, course0506, course0607, course0708,course0809,
                                  course0910, course1011])
                    pass
                    
                pass
            pass


        file1.close()
        pass


    def statistic2(self):
        '''Method to create the csv file for the second statistic (number of students on courses with "computadores" or "informática"'''

        courses_informatica = set()

        for x in range(len(self.course)):
            if ((self.course[x].lower().find('computadores') != -1) or (self.course[x].lower().find('informática') != -1)):
                courses_informatica.add(self.course[x])
                pass
            pass

        courses_informatica = list(courses_informatica)
        courses_informatica.sort()
        
        file2 = open('CSVStatistic2.csv', 'wb')
        spamWriter2 = csv.writer(file2, delimiter=',')

        spamWriter2.writerow(["Course", "Establishment",
                              "Organic Unit", "Year 1995-1996",
                              "Year 1996-1997", "Year 1997-1998",
                              "Year 1998-1999", "Year 1999-2000",
                              "Year 2000-2001", "Year 2001-2002",
                              "Year 2002-2003", "Year 2003-2004",
                              "Year 2004-2005", "Year 2005-2006",
                              "Year 2006-2007", "Year 2007-2008",
                              "Year 2008-2009", "Year 2009-2010",
                              "Year 2010-2011"])


        for x in range(len(courses_informatica)):
            for line in self.dataList:
                if(courses_informatica[x] == line[4]):
                    spamWriter2.writerow([courses_informatica[x], line[1], line[5], line[6], line[7],
                                          line[8], line[9], line[10], line[11],line[12], line[13], line[14], line[15],
                                          line[16], line[17], line[18], line[19], line[20], line[21]]) 
                    pass           
                pass
            pass
        


        file2.close()
        pass

    def statistic3(self):
        '''Method to create the csv file for the third statistic (courses by education level)'''
        
        total9596 = 0
        total9697 = 0
        total9798 = 0
        total9899 = 0
        total9900 = 0
        total0001 = 0
        total0102 = 0
        total0203 = 0
        total0304 = 0
        total0405 = 0
        total0506 = 0
        total0607 = 0
        total0708 = 0
        total0809 = 0
        total0910 = 0
        total1011 = 0
        
        file3 = open('CSVStatistic3.csv', 'wb')
        spamWriter3 = csv.writer(file3, delimiter=',')

        spamWriter3.writerow(["Education Level", "Year 1995-1996",
                              "Year 1996-1997", "Year 1997-1998",
                              "Year 1998-1999", "Year 1999-2000",
                              "Year 2000-2001", "Year 2001-2002",
                              "Year 2002-2003", "Year 2003-2004",
                              "Year 2004-2005", "Year 2005-2006",
                              "Year 2006-2007", "Year 2007-2008",
                              "Year 2008-2009", "Year 2009-2010",
                              "Year 2010-2011"])

        for x in range(len(self.education_level)):
            for line in self.dataList:
                if (self.education_level[x] == line[3]) and (line[6] != "-"):
                    total9596 = total9596 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[7] != "-"):
                    total9697 = total9697 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[8] != "-"):
                    total9798 = total9798 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[9] != "-"):
                    total9899 = total9899 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[10] != "-"):
                    total9900 = total9900 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[11] != "-"):
                    total0001 = total0001 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[12] != "-"):
                    total0102 = total0102 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[13] != "-"):
                    total0203 = total0203 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[14] != "-"):
                    total0304 = total0304 + 1
                    pass


                if (self.education_level[x] == line[3]) and (line[15] != "-"):
                    total0405 = total0405 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[16] != "-"):
                    total0506 = total0506 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[17] != "-"):
                    total0607 = total0607 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[18] != "-"):
                    total0708 = total0708 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[19] != "-"):
                    total0809 = total0809 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[20] != "-"):
                    total0910 = total0910 + 1
                    pass

                if (self.education_level[x] == line[3]) and (line[21] != "-"):
                    total1011 = total1011 + 1
                    pass

                pass

            spamWriter3.writerow([self.education_level[x], total9596, total9697, total9798, total9899, total9900, total0001, total0102, total0203, total0304, total0405,
                           total0506, total0607, total0708, total0809, total0910, total1011])

            total9596 = 0
            total9697 = 0
            total9798 = 0
            total9899 = 0
            total9900 = 0
            total0001 = 0
            total0102 = 0
            total0203 = 0
            total0304 = 0
            total0405 = 0
            total0506 = 0
            total0607 = 0
            total0708 = 0
            total0809 = 0
            total0910 = 0
            total1011 = 0
            pass
        pass

        file3.close()
        pass



    def statistic4(self):
        '''Method to create the csv file for the forth statistic (students by education level)'''

        total9596 = 0
        total9697 = 0
        total9798 = 0
        total9899 = 0
        total9900 = 0
        total0001 = 0
        total0102 = 0
        total0203 = 0
        total0304 = 0
        total0405 = 0
        total0506 = 0
        total0607 = 0
        total0708 = 0
        total0809 = 0
        total0910 = 0
        total1011 = 0

        file4 = open('CSVStatistic4.csv', 'wb')
        spamWriter4 = csv.writer(file4, delimiter=',')

        spamWriter4.writerow(["Education Level", "Year 1995-1996",
                              "Year 1996-1997", "Year 1997-1998",
                              "Year 1998-1999", "Year 1999-2000",
                              "Year 2000-2001", "Year 2001-2002",
                              "Year 2002-2003", "Year 2003-2004",
                              "Year 2004-2005", "Year 2005-2006",
                              "Year 2006-2007", "Year 2007-2008",
                              "Year 2008-2009", "Year 2009-2010",
                              "Year 2010-2011"])

        for x in range(len(self.education_level)):
            for line in self.dataList:
                if (self.education_level[x] == line[3]) and (line[6] != "-"):
                    total9596 = total9596 + int(line[6])
                    pass

                if (self.education_level[x] == line[3]) and (line[7] != "-"):
                    total9697 = total9697 + int(line[7])
                    pass

                if (self.education_level[x] == line[3]) and (line[8] != "-"):
                    total9798 = total9798 + int(line[8])
                    pass

                if (self.education_level[x] == line[3]) and (line[9] != "-"):
                    total9899 = total9899 + int(line[9])
                    pass

                if (self.education_level[x] == line[3]) and (line[10] != "-"):
                    total9900 = total9900 + int(line[10])
                    pass

                if (self.education_level[x] == line[3]) and (line[11] != "-"):
                    total0001 = total0001 + int(line[11])
                    pass

                if (self.education_level[x] == line[3]) and (line[12] != "-"):
                    total0102 = total0102 + int(line[12])
                    pass

                if (self.education_level[x] == line[3]) and (line[13] != "-"):
                    total0203 = total0203 + int(line[13])
                    pass

                if (self.education_level[x] == line[3]) and (line[14] != "-"):
                    total0304 = total0304 + int(line[14])
                    pass


                if (self.education_level[x] == line[3]) and (line[15] != "-"):
                    total0405 = total0405 + int(line[15])
                    pass

                if (self.education_level[x] == line[3]) and (line[16] != "-"):
                    total0506 = total0506 + int(line[16])
                    pass

                if (self.education_level[x] == line[3]) and (line[17] != "-"):
                    total0607 = total0607 + int(line[17])
                    pass

                if (self.education_level[x] == line[3]) and (line[18] != "-"):
                    total0708 = total0708 + int(line[18])
                    pass

                if (self.education_level[x] == line[3]) and (line[19] != "-"):
                    total0809 = total0809 + int(line[19])
                    pass

                if (self.education_level[x] == line[3]) and (line[20] != "-"):
                    total0910 = total0910 + int(line[20])
                    pass

                if (self.education_level[x] == line[3]) and (line[21] != "-"):
                    total1011 = total1011 + int(line[21])
                    pass

                pass

            spamWriter4.writerow([self.education_level[x], total9596, total9697, total9798, total9899, total9900, total0001, total0102, total0203, total0304, total0405,
                           total0506, total0607, total0708, total0809, total0910, total1011])

            total9596 = 0
            total9697 = 0
            total9798 = 0
            total9899 = 0
            total9900 = 0
            total0001 = 0
            total0102 = 0
            total0203 = 0
            total0304 = 0
            total0405 = 0
            total0506 = 0
            total0607 = 0
            total0708 = 0
            total0809 = 0
            total0910 = 0
            total1011 = 0
            pass

            pass

        file4.close()
        pass
    pass





            














